package dao;

import model.Goods;
import model.User;
import util.JDBCUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodsDao {
    JDBCUtil jdbcUtil = new JDBCUtil();

    // 查询所有商品
    public List<Goods> getAllGoods() {
        List<Goods> goodsList = new ArrayList<>();
        ResultSet resultSet = null;

        try {
            String sql = "SELECT * FROM goods";
            resultSet = jdbcUtil.prepareQuery(sql);
            while (resultSet.next()) {
                Goods goods = new Goods();
                goods.setGoods_id(resultSet.getInt("goods_id"));
                goods.setCreated_at(resultSet.getString("created_at"));
                goods.setGoods_image(resultSet.getString("goods_image"));
                goods.setGoods_info(resultSet.getString("goods_info"));
                goods.setGoods_category(resultSet.getString("goods_catergory"));
                goods.setGoods_price(resultSet.getDouble("goods_price"));
                goodsList.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            jdbcUtil.closeConnection();
        }

        return goodsList;
    }
    public List<Goods> selectAllGoods() {

        List<Goods> goodsList = new ArrayList<>();
        ResultSet rs = null;
        try  {
            String sql = "SELECT * FROM goods";
            rs = jdbcUtil.prepareQuery(sql);

            while (rs.next()) {
                int id = rs.getInt("goods_id");
                String createdAt = rs.getString("created_at");
                String image = rs.getString("goods_image");
                String info = rs.getString("goods_info");
                String category = rs.getString("goods_category");
                double price = rs.getDouble("goods_price");
                goodsList.add(new Goods(id, createdAt, image, info, category, price));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return goodsList;
    }

    // 根据ID删除商品
    public boolean deleteGoodsById(int goodsId) {
        try {
            String sql = "DELETE FROM goods WHERE goods_id = ?";
            return jdbcUtil.paramsUpdate(sql, goodsId);
        } finally {
            jdbcUtil.closeConnection();
        }
    }

    // 更新商品信息
    public boolean updateGoods(Goods goods) {
        String sql = "UPDATE goods SET goods_info = ?, goods_category = ?, goods_price = ? WHERE goods_id=?";

        // 打印调试信息，确保参数和 SQL 语句正确匹配
        System.out.println("Executing SQL: " + sql);
        System.out.println("Goods ID: " + goods.getGoods_id());
        System.out.println("Goods Info: " + goods.getGoods_info());
        System.out.println("Goods Category: " + goods.getGoods_category());
        System.out.println("Goods Price: " + goods.getGoods_price());

        // 调用 jdbcUtil 的 paramsUpdate 方法执行 SQL 更新操作
        boolean isUpdated = jdbcUtil.paramsUpdate(sql, goods.getGoods_info(), goods.getGoods_category(), goods.getGoods_price(), goods.getGoods_id());
        if (isUpdated) {
            System.out.println("Update successful.");
        } else {
            System.out.println("Update failed.");
        }
        return isUpdated;
    }

    public boolean updateUser(User user) {
        String sql = "UPDATE user SET username = ?, user_type = ?, phone = ?, email = ? WHERE user_id = ?";
        return jdbcUtil.paramsUpdate(sql, user.getUsername(), user.getUser_type(), user.getPhone(), user.getEmail(), user.getId());
    }
    public boolean addGoods(Goods goods) {
        String sql = "INSERT INTO goods (goods_image, goods_info, goods_category, goods_price, user_id) VALUES (?, ?, ?, ?,?)";
        return jdbcUtil.paramsUpdate(sql, goods.getGoods_image(), goods.getGoods_info(), goods.getGoods_category(), goods.getGoods_price(),goods.getUser_id());
    }


    // 根据商品名称查找商品
    public List<Goods> searchGoodsByName(String goodsName) {
        ResultSet resultSet = null;
        List<Goods> goodsList = new ArrayList<>();

        try {
            String sql = "SELECT * FROM goods WHERE goods_info LIKE ?";
            resultSet = jdbcUtil.prepareQuery(sql, "%" + goodsName + "%");

            while (resultSet.next()) {
                Goods goods = new Goods();
                goods.setGoods_id(resultSet.getInt("goods_id"));
                goods.setCreated_at(resultSet.getString("created_at"));
                goods.setGoods_image(resultSet.getString("goods_image"));
                goods.setGoods_info(resultSet.getString("goods_info"));
                goods.setGoods_category(resultSet.getString("goods_catergory"));
                goods.setGoods_price(resultSet.getDouble("goods_price"));
                goodsList.add(goods);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            jdbcUtil.closeConnection();
        }

        return goodsList;
    }

    public Goods searchGoodsBy_ID(int goods_id) {
        ResultSet resultSet = null;

        try {
            String sql = "SELECT * FROM goods WHERE goods_id = ?";
            resultSet = jdbcUtil.prepareQuery(sql, goods_id);

            if (resultSet.next()) {
                Goods goods = new Goods();
                goods.setGoods_id(resultSet.getInt("goods_id"));
                goods.setCreated_at(resultSet.getString("created_at"));
                goods.setGoods_image(resultSet.getString("goods_image"));
                goods.setGoods_info(resultSet.getString("goods_info"));
                goods.setGoods_category(resultSet.getString("goods_category")); // Corrected typo in column name
                goods.setGoods_price(resultSet.getDouble("goods_price"));

                return goods;
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error retrieving goods with ID: " + goods_id, e);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace(); // Consider logging this exception
                }
            }
            jdbcUtil.closeConnection(); // Assuming this method handles closing the connection
        }

        return null; // Return null if no goods were found with the specified ID
    }

    public List<Goods> searchGoodsByID(int  goods_id) {
        ResultSet resultSet = null;
        List<Goods> goodsList = new ArrayList<>();

        try {
            String sql = "SELECT * FROM goods WHERE goods_id = ?";
            resultSet = jdbcUtil.prepareQuery(sql, goods_id);

            while (resultSet.next()) {
                Goods goods = new Goods();
                goods.setGoods_id(resultSet.getInt("goods_id"));
                goods.setCreated_at(resultSet.getString("created_at"));
                goods.setGoods_image(resultSet.getString("goods_image"));
                goods.setGoods_info(resultSet.getString("goods_info"));
                goods.setGoods_category(resultSet.getString("goods_catergory"));
                goods.setGoods_price(resultSet.getDouble("goods_price"));
                goodsList.add(goods);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            jdbcUtil.closeConnection();
        }

        return goodsList;
    }
    public boolean delete(int goods_id){
        JDBCUtil jdbcUtil = new JDBCUtil();

        try {
            String sql = "DELETE FROM `goods` WHERE goods_id = ?";
            boolean pstmt = jdbcUtil.paramsUpdate(sql,goods_id);

            return  pstmt; // 如果影响的行数大于0，表示删除成功
        }
        // 处理数据库异常
        finally {
            jdbcUtil.closeConnection(); // 关闭数据库连接
        }
    }

}
